﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using ServiceStack.OrmLite;
using Common;
using System.Text;
using System.Data.SQLite;
using System.IO;
using EHR18.Models;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using OfficeOpenXml.Drawing.Chart;
using SPCLib;
using System.Drawing;

namespace EHR18
{
    public class TBAllService
    {
        public DateTime dtFrom { get; set; }
        public DateTime dtTo { get; set; }
        static readonly object locker = new object();

        static IDbConnection _db = Service.getConnection();

        List<TBAll> list = new List<TBAll>();

        public TBAllService(DateTime From, DateTime to)
        {
            dtFrom = From;
            dtTo = to;
            if (!_db.TableExists<TBAll>())
            {
                _db.CreateTable<TBAll>();
            }
            list = GetList();
        }

        public TBAllService(int n)
        {
            list = GetLastNList(n);
            dtFrom = list.First().TEST_TIME;
            dtTo = list.Last().TEST_TIME;
        }

        public List<TBAll> GetList()
        {
            return _db.Select<TBAll>(t => t.TEST_TIME > dtFrom && t.TEST_TIME < dtTo);
        }

        public DataTable GetTable(List<KVMapping> Namelist)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("时间", typeof(DateTime)));
            dt.Columns.Add(new DataColumn("工单", typeof(string)));
            dt.Columns.Add(new DataColumn("机种", typeof(string)));
            dt.Columns.Add(new DataColumn("操作员", typeof(string)));
            foreach (var v in Namelist.Where(t=>t.IsUse==true).Select(t=>t.Name))
            {
                dt.Columns.Add(new DataColumn(v, typeof(string)));
            }
            dt.Columns.Add(new DataColumn("判定",typeof(string)));
            foreach (var a in list)
            {
                var dr = dt.NewRow();
                dr["时间"] = a.TEST_TIME;
                dr["工单"] = a.MO;
                dr["机种"] = a.Model;
                dr["操作员"] = a.Operator;
                foreach (var b in a.LIST)
                {
                    foreach (var v in Namelist.Where(t => t.IsUse == true))
                    {
                        string[] conditons = v.Value.Split(':');
                        string Station = conditons[0];
                        string Item = conditons[1];
                        string subinfo = conditons[2];
                        if (b.Station == Station && b.Item == Item && b.SubInfo == subinfo)
                            dr[v.Name] = b.Value;
                    }
                }
                dr["判定"] = a.Result?"PASS":"FAIL";
                dt.Rows.Add(dr);
            }
            return dt;
        }

        public void Delete(DateTime df, DateTime dt)
        {
            _db.Delete<TBAll>(t => t.TEST_TIME > df && t.TEST_TIME < dt);
        }

        public static List<string> getNameStrings()
        {
            var a = _db.Single(_db.From<TBAll>().Where(t => t.Result == true).Where(t=>t.Model==GenneralConfig.instance.CurrentModel).OrderByDescending(t=>t.TEST_TIME).Take(1));
            var ret = new List<string>();

            //if(a==null)
            //     throw new Exception("未找到")
            foreach (var c in a.LIST)
            {
                var d = "";
                d = c.Station;
                d += ":" + c.Item;
                d += ":" + c.SubInfo;
                ret.Add(d);
            }
            return ret;
        }

        public List<TBAll> GetLastNList(int n)
        {
            var sql = _db.From<TBAll>().OrderByDescending(t => t.TEST_TIME).Take(n);
            return _db.Select(sql);
        }

        public List<double> GetValues( string item, string subinfo="Value")
        {
            double x = 0;
            var s = from a in list
                    from b in a.LIST
                    where b.Item == item
                    where b.SubInfo == subinfo
                    where double.TryParse(b.Value, out x)
                    select double.Parse(b.Value);
            return s.ToList();
        }
        public List<string> GetStrings(string station, string item, string subinfo)
        {
            var s = from a in list
                    from b in a.LIST
                    where b.Station == station
                    where b.Item == item
                    where b.SubInfo == subinfo
                    where !string.IsNullOrEmpty(b.Value)
                    select b.Value;
            return s.ToList();
        }

      public  Dictionary<string,FailRateStatistic> getFailRateStatistics()
        {
            var listFp = new Dictionary<string, FailRateStatistic>();
            foreach (var a in GenneralConfig.instance.module.TestItems.Where(t => t.IsUse == true))
            {
                List<string> list;
                if (a.Name == "图像")
                {
                    list = GetStrings(a.Name, "判定", "Result");
                }
                else
                {
                    list = GetStrings(a.Name, a.Name, "Result");
                }
                int pass = list.Where(t => t.ToUpper() == "PASS").Count();
                int fail = list.Where(t => t.ToUpper() == "FAIL").Count();
                listFp.Add(a.Name, new FailRateStatistic() { FailCount = fail, TotalCount = pass + fail });
            }
            return listFp;
        }

        public static void insert(TBAll tb)
        {

            lock (locker)
            {
                if (!_db.TableExists<TBAll>())
                {
                    _db.CreateTable<TBAll>();
                }
                _db.Insert(tb);
            }
        }

        public CPKData GetCPKDatas(double USL, double LSL, string item, string subinfo, double[] markvals, bool UseMarkvals)
        {
            var cpkdata = new CPKData() { USL = USL, LSL = LSL, MarkVals = markvals, ItemName = item, UseMarkVals = UseMarkvals };
            cpkdata.Datas = GetValues( item, subinfo);
            return cpkdata;
        }
    }
    public static class Service
    {
        public static DataTable getTestData(string tableName, string Model, string[] colName1, string[] colName2, DateTime begin, DateTime end)
        {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < colName1.Length; i++)
            {
                sb.Append(colName1[i] + " as [" + colName2[i] + "],");
            }
            string Part1 = sb.ToString().TrimEnd(',');
            try
            {
                string sql = string.Format("SELECT {0} FROM {1} WHERE MODEL='{2}' AND DATETIME(TEST_TIME) BETWEEN DATETIME('{3}') AND DATETIME('{4}')"
              , Part1, tableName, Model, begin.ToString("yyyy-MM-dd HH:mm:ss"), end.ToString("yyyy-MM-dd HH:mm:ss"));
                SQLiteConnectionStringBuilder scsb = new SQLiteConnectionStringBuilder();
                scsb.DataSource = System.IO.Path.Combine(Product.DATA_DIR, GenneralConfig.instance.CurrentModel + ".db3");
                SQLiteConnection Conn = new SQLiteConnection(scsb.ToString());
                SQLiteCommand cmd = Conn.CreateCommand();
                cmd.CommandText = sql;
                DataSet ds = new DataSet();
                if (Conn.State == ConnectionState.Closed)
                    Conn.Open();
                SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
                da.Fill(ds);
                da.Dispose();
                cmd.Dispose();
                Conn.Close();
                if (ds.Tables.Count > 0)
                    return ds.Tables[0];
                else
                    return null;
            }
            catch (Exception)
            {

                throw;
            }
        }

        public static IDbConnection getConnection()
        {
            if (!Directory.Exists(Product.DATA_DIR))
            {
                Directory.CreateDirectory(Product.DATA_DIR);
            }

            string DataSource = System.IO.Path.Combine(Product.DATA_DIR, GenneralConfig.instance.CurrentModel + ".db3");

            var con = new OrmLiteConnectionFactory(DataSource, SqliteDialect.Provider).Open();
            return con;
        }

    }

    public class ExcelReport
    {

        private static void SetReportTitle(string BeginTime, string EndTime, ExcelWorksheet ReportSheet)
        {
            ExcelRange range = ReportSheet.Cells[1, 1, 1, 12];
            range.Style.Font.Bold = true;
            range.Style.Font.Size = 16F;
            range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
            range.Style.Font.Name = "Microsoft Sans Serif";
            range.Merge = true;
            range.Value = string.Format("{0} Test Report",GenneralConfig.instance.CurrentModel);
            ExcelRange range1 = ReportSheet.Cells[2, 1, 2, 12];
            range1.Merge = true;
            range1.Value = string.Format("{0}~{1}", BeginTime, EndTime);
            range1.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
        }

        private static int SaveFailRateStatistics(TBAllService service,ExcelWorksheet ReportSheet, int irow)
        {
            int rowBegin = irow;
            Dictionary<string, FailRateStatistic> dicFailRate = service.getFailRateStatistics();
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 1], "Item");
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 2], "Fail Qty");
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 3], "Pass Qty");
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 4], "Total Qty");
            ExcelSetValueAndStyle(ReportSheet.Cells[irow, 5], "Fail Rate");
            irow++;
            foreach (var kv in dicFailRate)
            {
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 1], kv.Key);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 2], kv.Value.FailCount);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 3], kv.Value.PassCount);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 4], kv.Value.TotalCount);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 5], kv.Value.FailRate.ToString("P"));
                irow++;
            }
            return irow;
        }
        private static void ExcelSetValueAndStyle(ExcelRange range, object Value)
        {
            range.Style.Border.BorderAround(ExcelBorderStyle.Thin, Color.Black);
            range.AutoFitColumns(10, 80);
            range.Value = Value;
        }

        private static int SaveDetailStatistics(TBAllService service, ExcelWorksheet ReportSheet, int irow)
        {
            int rowBegin = irow;
            List<CPKData> cpkDatas = new List<CPKData>();
            foreach (var a in GenneralConfig.instance.module.ReportConfigItems)
            {
                cpkDatas.Add(service.GetCPKDatas(a.USL, a.LSL, a.Name, "Value", a.MarkVals, a.UseMarkVal));
            }
            List<ExcelChart> charts = new List<ExcelChart>();
            for (int idata = 0; idata < cpkDatas.Count; idata++)
            {
                int iRowBegin = irow;
                ReportSheet.Cells[irow, 1].Value = cpkDatas[idata].ItemName;
                irow++;
                Dictionary<string, double> dic = cpkDatas[idata].GetCPKDictionary();
                int rowCount = 0;
                foreach (var kp in dic)
                {
                    int row = irow;
                    int col = 1 + rowCount * 2;

                    ExcelSetValueAndStyle(ReportSheet.Cells[row, col], kp.Key);
                    ExcelSetValueAndStyle(ReportSheet.Cells[row, col + 1], kp.Value);
                    if (++rowCount > 5)
                    {
                        irow++;
                        rowCount = 0;
                    }
                }
                irow++;
                ReportSheet.Cells[irow, 1].Value = "Statistics by Groups:";
                irow++;
                List<GroupItem> gis = cpkDatas[idata].getGroupItems();
                //            ReportSheet.Cells[irow , 1].Value = "Item";
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 1], "Group");
                ExcelSetValueAndStyle(ReportSheet.Cells[irow + 1, 1], "Qty");
                ExcelSetValueAndStyle(ReportSheet.Cells[irow + 2, 1], "Percent");
                for (int i = 0; i < gis.Count; i++)
                {
                    ExcelSetValueAndStyle(ReportSheet.Cells[irow, 2 + i], gis[i].GroupTitle);
                    ExcelSetValueAndStyle(ReportSheet.Cells[irow + 1, 2 + i], gis[i].qty);
                    ReportSheet.Cells[irow + 2, 2 + i].Style.Numberformat.Format = @"0.00%";//设置为百分比显示
                    ExcelSetValueAndStyle(ReportSheet.Cells[irow + 2, 2 + i], gis[i].Percent);
                }
                irow = irow + 3;
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 1], "System Fail");
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 2], gis[0].systemFail);
                ExcelSetValueAndStyle(ReportSheet.Cells[irow, 3], gis[0].systemFail * 1.0 / gis[0].total);
                ReportSheet.Cells[irow, 3].Style.Numberformat.Format = @"0.00%";
                ExcelChart bar = ReportSheet.Drawings.AddChart(cpkDatas[idata].ItemName, eChartType.ColumnClustered);
                var ser = bar.Series.Add(ReportSheet.Cells[irow - 1, 2, irow - 1, 12], ReportSheet.Cells[irow - 3, 2, irow - 3, 12]);
                ser.Header = "占比";
                bar.Legend.Position = eLegendPosition.Right;
                irow++;

                //     bar.Legend.Add();
                bar.Title.Text = cpkDatas[idata].ItemName + "数据分布"; //设置图表的名称   
                bar.SetPosition(iRowBegin, 0, 13, 0);
                bar.SetSize(410, 240); //设置图表大小            
                bar.ShowHiddenData = true;
                charts.Add(bar);
                irow++;
            }
            int j = 0;
            for (int i = 0; i < charts.Count; i++)
            {
                int c = i % 2;
                j++;
                charts[i].SetPosition(irow, 0, 6 * c, 0);
                if (j > 1)
                {
                    irow = irow + 15;
                    j = 0;
                }

            }
            return irow;
        }
        public static void Saveexcel(TBAllService service, DataTable dt, DateTime BeginTime, DateTime EndTime, string templateFilePath,string fileName)
        {

       //     string fileName = GenneralConfig.instance.OutputPath + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            string Dir = Path.GetDirectoryName(fileName);
            string strBeginTime = BeginTime.ToString("yyyy-MM-dd HH:mm:ss");
           string  strEndTime = EndTime.ToString("yyyy-MM-dd HH:mm:ss");
            if (!Directory.Exists(Dir))
            {
                Directory.CreateDirectory(Dir);
            }
            FileInfo file = new FileInfo(fileName);
            using (ExcelPackage package = new ExcelPackage(file, new FileInfo(templateFilePath)))
            {
                //  ExcelWorksheet ReportSheet = package.Workbook.Worksheets.Add("Report");
                ExcelWorksheet ReportSheet = package.Workbook.Worksheets["Report"];
                //   package.Workbook.
                ReportSheet.View.ShowGridLines = false;
                ReportSheet.Cells[1, 1].Value = string.Format("{0} Test Report ({1}-{2})", GenneralConfig.instance.CurrentModel, BeginTime, EndTime);
                SetReportTitle(strBeginTime, strEndTime, ReportSheet);
                int irow = 3;
                irow = SaveFailRateStatistics(service, ReportSheet, irow);
                irow++;
                irow = SaveDetailStatistics(service, ReportSheet, irow);
                ExcelWorksheet ws = package.Workbook.Worksheets["Data"];
                ws.Cells["A1"].LoadFromDataTable(dt, true);
                package.Save(); //保存文件   
            }
        }
    }

}
